A QueryDef object is a stored definition of a query in a Microsoft Jet database, or a temporary definition of a query in an ODBCDirect workspace.
Remarks
You can use the QueryDef object to define a query. For example, you can:
In a Microsoft Jet workspace, you can also:
In an ODBCDirect workspace, you can also:
You can also create temporary QueryDef objects. Unlike permanent QueryDef objects, temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection. Temporary QueryDef objects are useful for queries that you must run repeatedly during run time but do not not need to save to disk, particularly if you create their SQL statements during run time.
You can think of a permanent QueryDef object in a Microsoft Jet workspaces as a compiled SQL statement. If you execute a query from a permanent QueryDef object, the query will run faster than if you run the equivalent SQL statement from the OpenRecordset method. This is because the Microsoft Jet database engine doesn't need to compile the query before executing it.
The preferred way to use the native SQL dialect of an external database engine accessed through the Microsoft Jet database engine is through QueryDef objects. For example, you can create a Microsoft SQL Server query and store it in a QueryDef object. When you need to use a non-Microsoft Jet database engine SQL query, you must provide a Connect property string that points to the external data source. Queries with valid Connect properties bypass the Microsoft Jet database engine and pass the query directly to the external database server for processing.
To create a new QueryDef object, use the CreateQueryDef method. In a Microsoft Jet workspace, if you supply a string for the name argument or if you explicitly set the Name property of the new QueryDef object to a non�zero-length string, you will create a permanent QueryDef that will automatically be appended to the QueryDefs collection and saved to disk. Supplying a zero-length string as the name argument or explicitly setting the Name property to a zero-length string will result in a temporary QueryDef object.
In an ODBCDirect workspace, a QueryDef is always temporary. The QueryDefs collection contains all open QueryDef objects. When a QueryDef is closed, it is automatically removed from the QueryDefs collection.
To refer to a QueryDef object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
QueryDefs(0)
QueryDefs("name")
QueryDefs![name]
You can refer to temporary QueryDef objects only by the object variables that you have assigned to them.